package admission.ecnu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.jsp.tagext.TryCatchFinally;

import admission.ecnu.db.DBConnection;
import admission.ecnu.po.User;

public class UserDAO {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	public UserDAO() {
		// TODO Auto-generated constructor stub
		this.conn = DBConnection.getConnection();
	}
	
	public List<User> userSelect(){
		List<User> list = new ArrayList<User>();
		try {
			this.pstmt = this.conn.prepareStatement("select id,username,userpass from t_user");
			this.rs = this.pstmt.executeQuery();
			while(this.rs.next()){
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setUserpass(rs.getString("userpass"));
				list.add(user);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.rs.close();
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return list;
	}
	
	public int userInsert(String username,String userpass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("insert into t_user(username,userpass) value(?,?)");
			this.pstmt.setString(1,username);
			this.pstmt.setString(2,userpass);
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public int userDelete(String id){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("delete from t_user where id in(?)");
			this.pstmt.setString(1, id);
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public int userUpdate(String id,String username,String userpass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("update t_user set username=?,userpass=? where id=?");
			this.pstmt.setString(1,	username);
			this.pstmt.setString(2, userpass);
			this.pstmt.setString(3, id);
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public List<User> userUpdateLoad(String id){
		List<User> list = new ArrayList<User>();
		try {
			this.pstmt = this.conn.prepareStatement("select username,userpass from t_user where id = ?");
			this.pstmt.setString(1, id);
			this.rs = this.pstmt.executeQuery();
			while(this.rs.next()){
				User user = new User();
				user.setUsername(rs.getString("username"));
				user.setUserpass(rs.getString("userpass"));
				list.add(user);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
				this.rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return list;
	}
	
	
	
	
	
}
